package com.xms.datacore.provider;

import com.xms.datacore.SqlDescriptor;
import com.xms.utils.CollectionUtil;
import com.xms.utils.RegexUtil;
import org.apache.ibatis.session.SqlSession;
import org.joda.time.DateTime;
import org.joda.time.LocalDate;
import org.joda.time.LocalDateTime;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.SqlSessionUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.Assert;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

/**
 * <p>
 * DataBase操作类
 * </p>
 *
 * @author migo
 * @since 2021/8/31
 */
@Service
public class DataBase implements IDataBase {
    private SqlSessionTemplate _sqlSessionTemplate;

    @Autowired
    public void DataBase(SqlSessionTemplate sqlSessionTemplate) {
        _sqlSessionTemplate = sqlSessionTemplate;
    }

    @Override
    public boolean execute(String sql) throws SQLException {
        PreparedStatement pst = null;
        SqlSession session = this.getSqlSession();
        try {
            pst = session.getConnection().prepareStatement(sql);
            pst.execute();
            this.closeSqlSession(session);
            return true;
        } catch (SQLException e) {
            throw e;
        } finally {
            this.closeSqlSession(session);
        }
    }

    @Override
    public boolean execute(SqlDescriptor sql) throws SQLException {
        PreparedStatement pst = null;
        SqlSession session = this.getSqlSession();
        try {
            pst = session.getConnection().prepareStatement(sql.getFinalSql());
            if (CollectionUtil.notEmpty(sql.getFinalArgs()))
                setStatementArgs(pst, sql.getFinalArgs());
            pst.execute();
            this.closeSqlSession(session);
            return true;
        } catch (SQLException e) {
            throw e;
        } finally {
            this.closeSqlSession(session);
        }
    }

    @Override
    public boolean execute(SqlSession session, String sql) throws SQLException {
        PreparedStatement pst = null;
        pst = session.getConnection().prepareStatement(sql);
        pst.execute();
        return true;
    }

    @Override
    public boolean execute(SqlSession session, SqlDescriptor sql) throws SQLException {
        PreparedStatement pst = null;
        pst = session.getConnection().prepareStatement(sql.getFinalSql());
        if (CollectionUtil.notEmpty(sql.getFinalArgs()))
            setStatementArgs(pst, sql.getFinalArgs());
        pst.execute();
        return true;
    }

    @Override
    public ResultSet executeQuery(String sql) throws SQLException {
        PreparedStatement pst = null;
        SqlSession session = this.getSqlSession();
        try {
            pst = session.getConnection().prepareStatement(sql);
            ResultSet result = pst.executeQuery();
            this.closeSqlSession(session);
            return result;
        } catch (SQLException e) {
            throw e;
        } finally {
            this.closeSqlSession(session);
        }
    }

    @Override
    public ResultSet executeQuery(SqlDescriptor sql) throws SQLException {
        PreparedStatement pst = null;
        SqlSession session = this.getSqlSession();
        try {
            pst = session.getConnection().prepareStatement(sql.getFinalSql());
            setStatementArgs(pst, sql.getFinalArgs());
            ResultSet result = pst.executeQuery();
            this.closeSqlSession(session);
            return result;
        } catch (SQLException e) {
            throw e;
        } finally {
            this.closeSqlSession(session);
        }
    }

    @Override
    public List<ResultSet> executeMultiQuery(SqlDescriptor... sqls) throws SQLException {
        if (sqls != null && sqls.length > 0) {
            List<ResultSet> result = new ArrayList<>();
            SqlSession session = this.getSqlSession();
            try {
                for (SqlDescriptor sql : sqls) {
                    PreparedStatement pst = null;
                    pst = session.getConnection().prepareStatement(sql.getFinalSql());
                    setStatementArgs(pst, sql.getFinalArgs());
                    ResultSet rs = pst.executeQuery();
                    result.add(rs);
                }
                this.closeSqlSession(session);
                return result;
            } catch (SQLException e) {
                throw e;
            } finally {
                this.closeSqlSession(session);
            }
        }
        return null;
    }

    @Override
    public ResultSet executeQuery(SqlSession session, String sql) throws SQLException {
        Assert.notNull(session, "session must not be null");
        PreparedStatement pst = null;
        pst = session.getConnection().prepareStatement(sql);
        ResultSet result = pst.executeQuery();
        return result;
    }

    @Override
    public ResultSet executeQuery(SqlSession session, SqlDescriptor sql) throws SQLException {
        Assert.notNull(session, "session must not be null");
        PreparedStatement pst = null;
        pst = session.getConnection().prepareStatement(sql.getFinalSql());
        setStatementArgs(pst, sql.getFinalArgs());
        ResultSet result = pst.executeQuery();
        return result;
    }

    @Override
    public List<ResultSet> executeMultiQuery(SqlSession session, SqlDescriptor... sqls) throws SQLException {
        Assert.notNull(session, "session must not be null");
        if (sqls != null && sqls.length > 0) {
            List<ResultSet> result = new ArrayList<>();
            for (SqlDescriptor sql : sqls) {
                PreparedStatement pst = null;
                pst = session.getConnection().prepareStatement(sql.getFinalSql());
                setStatementArgs(pst, sql.getFinalArgs());
                ResultSet rs = pst.executeQuery();
                result.add(rs);
            }
            return result;
        }
        return null;
    }

    /**
     * 获取sqlSession
     *
     * @return
     */
    public SqlSession getSqlSession() {
        return SqlSessionUtils.getSqlSession(_sqlSessionTemplate.getSqlSessionFactory(),
                _sqlSessionTemplate.getExecutorType(), _sqlSessionTemplate.getPersistenceExceptionTranslator());
    }

    /**
     * 关闭sqlSession
     *
     * @param session
     */
    public void closeSqlSession(SqlSession session) {
        //其他数据库类型手动关闭连接会导致结果集已关闭异常
        //if(DataBaseSetting.dataBaseType.equals(DataBaseType.PGSQL)) {
        SqlSessionUtils.closeSqlSession(session, _sqlSessionTemplate.getSqlSessionFactory());
        //}
    }

    private void setStatementArgs(PreparedStatement pst, Object... args) throws SQLException {
        if (CollectionUtil.notEmpty(args)) {
            int n = 1;
            for (int i = 0; i < args.length; i++) {
                if (args[i] instanceof List) {
                    Object[] a = ((List) args[i]).toArray();
                    for (int j = 0; j < a.length; j++) {
                        setStatementArg(pst, n, a[j]);
                        n++;
                    }
                } else {
                    setStatementArg(pst, n, args[i]);
                    n++;
                }
            }
        }
    }

    private void setStatementArg(PreparedStatement pst, Integer argIndex, Object value) throws SQLException {
        if (value instanceof LocalDate) {
            pst.setDate(argIndex, Date.valueOf(String.valueOf(value)));
        } else if (value instanceof DateTime) {
            pst.setTime(argIndex, java.sql.Time.valueOf(String.valueOf(((DateTime) value).toLocalDateTime())));
        } else if (value instanceof LocalDateTime) {
            String t = new LocalDateTime(value).toString("yyyy-MM-dd HH:mm:ss");
            pst.setTimestamp(argIndex, java.sql.Timestamp.valueOf(t));
        } else {
            if (value != null && RegexUtil.isUUID(value.toString())) {
                pst.setObject(argIndex, UUID.fromString(value.toString()));
            } else {
                pst.setObject(argIndex, value);
            }
        }
    }
}
